package com.dhcc.DRGsHosp.application.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.openkoala.koalacommons.resourceloader.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.OrderApplication;
import com.dhcc.DRGsHosp.core.domain.Order;

/**
 * @title		:OrderApplicationImpl.java
 * @description	:
 * @author		:lileizheng
 * @date		:2015年11月12日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class OrderApplicationImpl implements OrderApplication {
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	
	private EntityRepository getRepository() {
		EntityRepository repository = null;
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
	
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getOrderQuotaValue1' + #order.dateFrom + #order.dateTo + "
			+ "#order.pType + #order.depGroupCode + #order.depCode + "
			+ "#order.adrgCode + #order.drgCode + #order.isExceeding ")
	public Map<String, Object> getOrderQuotaValue1(Order order){
		
		String dateFrom = order.getDateFrom();
		String dateTo = order.getDateTo();
		
		String pType = order.getpType();
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String drgCode = order.getDrgCode();
		String isExceeding = order.getIsExceeding();
		StringBuilder sql = new StringBuilder();
		
		sql.append("SELECT "
				+ " count(distinct part.order_mr_id) TOTALCASECNT, "
				+ " round(avg(part.costamount)/10000, 2) AVGFEE,   "
				+ " round(sum(part.costamount)/10000,2) TOTALFEE, "
				+ " round(sum(case when part.dcc_code = 'med'then part.order_total_fee else 0 end )/10000,2) MEDFEE,  "
				+ " round(sum(case when part.dcc_code = 'tech'then part.order_total_fee else 0 end )/10000,2) TECHFEE, "
				+ " round(sum(case when part.dcc_code = 'manag'then part.order_total_fee else 0 end )/10000,2) MANAGFEE, "
				+ " round(sum(case when part.dcc_code = 'drug'then part.order_total_fee else 0 end )/10000,2) DRUGFEE, "
				+ " round(sum(case when part.dcc_code = 'nurs'then part.order_total_fee else 0 end )/10000,2) NURSFEE, "
				+ " round(sum(part.order_total_fee)/10000,2) ORDERTOTALFEE,  "
				+ " round(avg(part.order_total_fee)/10000,2) ORDERAVGFEE,  "
				+ " case when count(distinct(part.order_mr_id))=0 then 0 else round(count(1) / count(distinct(part.order_mr_id)), 1) end ORDERSAVG,  "
				+ " count(distinct (case when part.is_trans_dep=1 then part.order_mr_id else null end)) TRANSDEPS,  "
				+ " count(distinct part.order_id||part.order_desc) ORDERCOUNT "
				+ " FROM DRGS.T_ORDER_RANGE_month part  "
				+ " where 1=1  "
				+ " and part.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd')  ");

		if ("1".equals(pType)) {
			sql.append(" and part.ismedins = 1 \n ");
		}
		if (StringUtils.hasLength(depGroupCode)) {
			sql.append(" and part.depgroup_code='" + depGroupCode + "' \n ");
		}
		if (StringUtils.hasLength(depCode)) {
			sql.append(" and part.dep_code='" + depCode + "' \n ");
		}
		if (StringUtils.hasLength(adrgCode)) {
			sql.append(" and part.adrg_code='" + adrgCode + "' \n ");
		}
		if (StringUtils.hasLength(drgCode)) {
			sql.append(" and part.drgs_code='" + drgCode + "' \n ");
		}
		if ("1".equals(isExceeding)) {
			sql.append(" and part.costamount>part.benchmark \n ");
		} else if ("0".equals(isExceeding)) {
			sql.append(" and part.costamount<=part.benchmark \n ");
		}
		
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		if(resultList.size()>0){
			return resultList.get(0);
		}
		return null;
	}
	
	@Cacheable(value = "drgscom3.1.1", key = "'getOrderAnalysisResult' + #order.dateFrom + #order.dateTo + "
			+ "#order.pType + #order.depGroupCode + #order.depCode + "
			+ "#order.adrgCode + #order.drgCode + '-' + #order.isExceeding + '-' + "
			+ "#order.isHighOrder + '-' + #order.isPrimaryFee + '-' + #order.isPrimaryUse + '-' + "
			+ "#order.notExceedingOrders + '-' + #order.orderId ")
	@Override
	public List<Map<String, Object>> getOrderAnalysisResult(Order order){
		String orderId = order.getOrderId();
		String dateFrom = order.getDateFrom();
		String dateTo = order.getDateTo();
		String pType = order.getpType();
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String drgCode = order.getDrgCode();
		String isExceeding = order.getIsExceeding();
		String isHighOrder = order.getIsHighOrder();
		String isPrimaryFee = order.getIsPrimaryFee();
		String isPrimaryUse = order.getIsPrimaryUse();
		String notExceedingOrders = order.getNotExceedingOrders();
		StringBuilder paramStr = new StringBuilder();
		if(StringUtils.hasLength(depGroupCode)){
			paramStr.append(" and depGroup_Code='"+depGroupCode+"' ");
		}
		if(StringUtils.hasLength(depCode)){
			paramStr.append(" and dep_Code='"+depCode+"' ");
		}
		if("1".equals(pType)){
			paramStr.append(" and a.ismedins = 1 ");
		}
		if(StringUtils.hasLength(adrgCode)){
			paramStr.append(" and adrg_Code='"+adrgCode+"' ");
		}
		if(StringUtils.hasLength(drgCode)){
			paramStr.append(" and drgs_code='"+drgCode+"'");
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" select * from( \n ");
		sql.append(" select a.*, nvl(b.notExcellent, 0) notExcellent, c.allCount, round(a.mrCount/c.allCount*100,2) usageRate, \n ");
		sql.append(" round(sum(a.OrderTotalFee) over(order by a.OrderTotalFee desc)/c.totalFee*100,2) mcRate \n ");
		sql.append(" from ( \n ");
		sql.append(" select ORDER_ID OrderId, ORDER_DESC OrderDesc, \n ");
		sql.append(" max(order_unit_price) maxUnitPrice, \n ");
		sql.append(" count(distinct order_mr_id) mrCount, \n ");
		sql.append(" sum(order_total_fee) OrderTotalFee, \n ");
		sql.append(" round(sum(order_total_fee)/count(distinct order_mr_id),2) OrderAvgFee, \n ");
		sql.append(" sum(order_quantity) OrderQuality, \n ");
		sql.append(" count(order_mr_id) OrderCount \n ");
		sql.append(" from drgs.t_order_range_month a  \n ");
		sql.append(" where exists( \n ");
		sql.append(" select 1 from( \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id \n ");
		sql.append(" ) b where a.order_id=b.order_id \n ");
		sql.append(" ) \n ");
		
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and a.costamount>benchmark  \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and a.costamount<=benchmark  \n ");
		}
		sql.append(paramStr.toString());
		sql.append(" group by order_id, order_desc \n ");
		sql.append(" )a left join ( \n ");
		sql.append(" select order_id, \n ");
		sql.append(" count(distinct order_mr_id) notExcellent \n ");
		sql.append(" from drgs.t_order_range_month a  \n ");
		sql.append(" where exists( \n ");
		sql.append(" select 1 from( \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id \n ");
		sql.append(" ) b where a.order_id=b.order_id \n ");
		sql.append(" ) \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" and a.costamount<=benchmark  \n ");
		sql.append(paramStr.toString());
		sql.append(" group by order_id \n ");
		sql.append(" ) \n ");
		sql.append(" b on a.OrderId=b.order_id \n ");
		sql.append(" ,( \n ");
		sql.append(" select count(distinct order_mr_id) allCount, \n ");
		sql.append(" sum(a.order_total_fee) totalFee  \n ");
		sql.append(" from drgs.t_order_range_month a  \n ");
		sql.append(" where exists( \n ");
		sql.append(" select 1 from( \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id \n ");
		sql.append(" ) b where a.order_id=b.order_id \n ");
		sql.append(" ) \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and a.costamount>benchmark  \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and a.costamount<=benchmark  \n ");
		}
		
		sql.append(paramStr.toString());
		sql.append(" ) c \n ");
		sql.append(" ) a where 1=1 \n ");
		if(StringUtils.hasLength(orderId)){
			sql.append(" and OrderId='"+orderId+"' \n ");
		}
		// 高值医嘱
		if("1".equals(isHighOrder)){
			sql.append(" and maxunitprice>500 \n ");
		}else if("0".equals(isHighOrder)){
			sql.append(" and maxunitprice<=500 \n ");
		} else if (StringUtils.hasLength(isHighOrder)) {
			sql.append(" and maxunitprice>"+isHighOrder+" \n ");
		}

		//主要使用
		if("1".equals(isPrimaryUse)){
			sql.append(" and usageRate>60 \n ");
		}else if("0".equals(isPrimaryUse)){
			sql.append(" and usageRate<=60 \n ");
		} else if (StringUtils.hasLength(isPrimaryUse)) {
			sql.append(" and usageRate>"+isPrimaryUse+" \n ");
		}

		//主要费用
		if("1".equals(isPrimaryFee)){
			sql.append(" and mcRate>80 \n ");
		}else if("0".equals(isPrimaryFee)){
			sql.append(" and mcRate<=80 \n ");
		} else if (StringUtils.hasLength(isPrimaryFee)) {
			sql.append(" and mcRate>"+isPrimaryFee+" \n ");
		}

		//未超标使用人次
		if(StringUtils.hasLength(notExceedingOrders)){
			sql.append(" and notExcellent>"+notExceedingOrders+" \n ");
		}
		
		List<Map<String, Object>> resultList =jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	
	@Cacheable(value = "drgscom3.1.1", key = "'getOrderAnalysisResultTotal' + #order.dateFrom + #order.dateTo + "
			+ "#order.pType + #order.depGroupCode + #order.depCode + "
			+ "#order.adrgCode + #order.drgCode + '-' + #order.isExceeding + '-' + "
			+ "#order.isHighOrder + '-' + #order.isPrimaryFee + '-' + #order.isPrimaryUse + '-' + "
			+ "#order.notExceedingOrders")
	@Override
	public List<Map<String, Object>> getOrderAnalysisResultTotal(Order order){
		String orderId = order.getOrderId();
		String dateFrom = order.getDateFrom();
		String dateTo = order.getDateTo();
		String pType = order.getpType();
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String drgCode = order.getDrgCode();
		String isExceeding = order.getIsExceeding();
		String isHighOrder = order.getIsHighOrder();
		String isPrimaryFee = order.getIsPrimaryFee();
		String isPrimaryUse = order.getIsPrimaryUse();
		String notExceedingOrders = order.getNotExceedingOrders();
		StringBuilder paramStr = new StringBuilder();
		if(StringUtils.hasLength(depGroupCode)){
			paramStr.append(" and depGroup_Code='"+depGroupCode+"' ");
		}
		if(StringUtils.hasLength(depCode)){
			paramStr.append(" and dep_Code='"+depCode+"' ");
		}
		if("1".equals(pType)){
			paramStr.append(" and a.ismedins = 1 ");
		}
		if(StringUtils.hasLength(adrgCode)){
			paramStr.append(" and adrg_Code='"+adrgCode+"' ");
		}
		if(StringUtils.hasLength(drgCode)){
			paramStr.append(" and drgs_code='"+drgCode+"'");
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(order_id) orderCount, max(mrCount) mrCount, sum(order_pt) orderPt, round(sum(order_pt)/max(mrcount), 2) orderAvgPt, \n ");
		sql.append(" sum(orderTotalFee) orderTotalFee, 0 as totalFee, round(sum(orderTotalFee)/max(mrcount), 2) orderAvgFee, 0 as orderFeeRatio from( \n ");
		sql.append(" select a.*, nvl(b.notExcellent, 0) notExcellent, c.allCount, round(a.mrCount/c.allCount*100,2) usageRate,  \n ");
		sql.append(" round(sum(a.orderTotalFee) over(order by a.orderTotalFee desc)/c.totalFee*100,2) mcRate  \n ");
		sql.append(" from (  \n ");
		sql.append(" select order_id, order_desc,  \n ");
		sql.append(" max(order_unit_price) maxUnitPrice,  \n ");
		sql.append(" count(distinct order_mr_id) mrCount,  \n ");
		sql.append(" sum(order_total_fee) orderTotalFee,  \n ");
		sql.append(" round(sum(order_total_fee)/count(distinct order_mr_id),2),  \n ");
		sql.append(" sum(order_quantity),  \n ");
		sql.append(" count(a.id) order_pt, \n ");
		sql.append(" count(order_mr_id)  \n ");
		sql.append(" from drgs.t_order_range_month a   \n ");
		sql.append(" where exists(  \n ");
		sql.append(" select 1 from(  \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a  \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id \n ");
		sql.append(" ) b where a.order_id=b.order_id \n ");
		sql.append(" ) \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and a.costamount>benchmark  \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and a.costamount<=benchmark  \n ");
		}
		sql.append(paramStr.toString());
		sql.append(" group by order_id, order_desc  \n ");
		sql.append(" )a left join (  \n ");
		sql.append(" select order_id,  \n ");
		sql.append(" count(distinct order_mr_id) notExcellent  \n ");
		sql.append(" from drgs.t_order_range_month a   \n ");
		sql.append(" where exists(  \n ");
		sql.append(" select 1 from(  \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a  \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id  \n ");
		sql.append(" ) b where a.order_id=b.order_id  \n ");
		sql.append(" )  \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" and a.costamount<=benchmark  \n ");
		sql.append(paramStr.toString());
		sql.append(" group by order_id  \n ");
		sql.append(" )  \n ");
		sql.append(" b on a.order_id=b.order_id  \n ");
		sql.append(" ,(  \n ");
		sql.append(" select count(distinct order_mr_id) allCount,  \n ");
		sql.append(" sum(a.order_total_fee) totalFee   \n ");
		sql.append(" from drgs.t_order_range_month a   \n ");
		sql.append(" where exists(  \n ");
		sql.append(" select 1 from(  \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a  \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id  \n ");
		sql.append(" ) b where a.order_id=b.order_id  \n ");
		sql.append(" )  \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and a.costamount>benchmark  \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and a.costamount<=benchmark  \n ");
		}
		sql.append(paramStr.toString());
		sql.append(" ) c  \n ");
		sql.append(" ) a where 1=1  \n ");
		if(StringUtils.hasLength(orderId)){
			sql.append(" and order_id='"+orderId+"' \n ");
		}
		//高值医嘱
		if("1".equals(isHighOrder)){
			sql.append(" and maxunitprice>500 \n ");
		}else if("0".equals(isHighOrder)){
			sql.append(" and maxunitprice<=500 \n ");
		} else if (StringUtils.hasLength(isHighOrder)){
			sql.append(" and maxunitprice>"+isHighOrder+" \n ");
		}

		//主要使用
		if("1".equals(isPrimaryUse)){
			sql.append(" and usageRate>60 \n ");
		}else if("0".equals(isPrimaryUse)){
			sql.append(" and usageRate<=60 \n ");
		} else if (StringUtils.hasLength(isPrimaryUse)){
			sql.append(" and usageRate>"+isPrimaryUse+" \n ");
		}

		//主要费用
		if("1".equals(isPrimaryFee)){
			sql.append(" and mcRate>80 \n ");
		}else if("0".equals(isPrimaryFee)){
			sql.append(" and mcRate<=80 \n ");
		} else if (StringUtils.hasLength(isPrimaryFee)) {
			sql.append(" and mcRate>"+isPrimaryFee+" \n ");
		}

		//未超标使用人次
		if(StringUtils.hasLength(notExceedingOrders)){
			sql.append(" and notExcellent>"+notExceedingOrders+" \n ");
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	
	@Cacheable(value = "drgscom3.1.1", key = "'getOrderAnalysisResultTotalFee' + #order.dateFrom + #order.dateTo + "
			+ "#order.pType + #order.depGroupCode + #order.depCode + "
			+ "#order.adrgCode + #order.drgCode + '-' + #order.isExceeding + '-' + "
			+ "#order.isHighOrder + '-' + #order.isPrimaryFee + '-' + #order.isPrimaryUse + '-' + "
			+ "#order.notExceedingOrders")
	@Override
	public List<Map<String, Object>> getOrderAnalysisResultTotalFee(Order order){
		String dateFrom = order.getDateFrom();
		String dateTo = order.getDateTo();
		String pType = order.getpType();
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String drgCode = order.getDrgCode();
		String isExceeding = order.getIsExceeding();
		StringBuilder paramStr = new StringBuilder();
		if(StringUtils.hasLength(depGroupCode)){
			paramStr.append(" and depGroup_Code='"+depGroupCode+"' ");
		}
		if(StringUtils.hasLength(depCode)){
			paramStr.append(" and dep_Code='"+depCode+"' ");
		}
		if("1".equals(pType)){
			paramStr.append(" and a.ismedins = 1 ");
		}
		if(StringUtils.hasLength(adrgCode)){
			paramStr.append(" and adrg_Code='"+adrgCode+"' ");
		}
		if(StringUtils.hasLength(drgCode)){
			paramStr.append(" and drgs_code='"+drgCode+"'");
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" select sum(costamount) totalFee \n ");
		sql.append(" from drgs.t_order_range_month a   \n ");
		sql.append(" where exists(  \n ");
		sql.append(" select 1 from(  \n ");
		sql.append(" select order_id, max(order_unit_price) from drgs.t_order_range_month a  \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		sql.append(" group by order_id  \n ");
		sql.append(" ) b where a.order_id=b.order_id  \n ");
		sql.append(" )  \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and a.costamount>benchmark  \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and a.costamount<=benchmark  \n ");
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}

	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getADRGBy' +"
			+ "#order.depGroupCode + #order.depCode + "
			+ "#order.isExceeding +#groupScheme")
	public List<Map<String, Object>> getADRGBy(Order order,String groupScheme) {
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String isExceeding = order.getIsExceeding();
		StringBuilder sql = new StringBuilder();
		sql.append(" select adrg_code code, adrg_code||'-'||adrg_desc \"DESC\" from( ");//desc作别名，特殊处理
		sql.append(" select distinct g.adrg_code, g.adrg_desc, rank()over(partition by g.adrg_code order by v.add_date) discount \n ");
		sql.append(" from drgs.MR_IPINFO b \n ");
		sql.append(" left join drgs.t_patientdrg c  \n ");
		sql.append(" on c.parid=b.pmainindex  and c.ver_region='"+groupScheme+"' \n ");
		sql.append(" left join drgs.t_department d  \n ");
		sql.append(" on b.P00300001=d.dep_code  \n ");
		sql.append(" left join drgs.t_depgroup e  \n ");
		sql.append(" on e.depgroup_code=d.pardepgroup_code   \n ");
		sql.append(" left join drgs.t_drgs f  \n ");
		sql.append(" on f.drg_code=c.pardrg_code and f.ver_code=c.ver_code  \n ");
		sql.append(" left join drgs.t_adrg g  \n ");
		sql.append(" on g.adrg_code=f.adrg_code and f.ver_code=g.ver_code  \n ");
		sql.append(" left join drgs.t_drgversion v \n ");
		sql.append(" on v.ver_code=f.ver_code \n ");
		sql.append(" left join drgs.t_medins m  \n ");
		sql.append(" on m.parid=b.pmainindex  \n ");
		sql.append(" where 1=1 and g.adrg_code is not null  \n ");
		if(StringUtils.hasLength(depGroupCode)){
			sql.append(" and e.depgroup_code='"+depGroupCode+"' \n ");
		}
		if(StringUtils.hasLength(depCode)){
			sql.append(" and d.dep_code='"+depCode+"' \n ");
		}
		if("1".equals(isExceeding)){
			sql.append(" and b.P00800000>avgFeeStd.manage_benchmark \n ");
		}
		sql.append(" ) where discount=1 ");
		List<Map<String, Object>> list = jdbcTemplateObj.queryForList(sql.toString());
		return list;
	}

	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getDRGsBy' +"
			+ "#order.adrgCode + #order.depGroupCode + #order.depCode + "
			+ "#order.isExceeding +#groupScheme")
	public List<Map<String, Object>> getDRGsBy(Order order,String groupScheme) {
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String isExceeding = order.getIsExceeding();
		StringBuilder sql = new StringBuilder();
		sql.append(" select drg_code code, drg_code||'-'||drg_desc \"DESC\" from( ");//desc作别名，特殊处理
		sql.append(" select distinct f.drg_code, f.drg_desc, rank()over(partition by f.drg_code order by v.add_date) discount \n ");
		sql.append(" from drgs.MR_IPINFO b \n ");
		sql.append(" left join drgs.t_patientdrg c  \n ");
		sql.append(" on c.parid=b.pmainindex  and c.ver_region='"+groupScheme+"' \n ");
		sql.append(" left join drgs.t_department d  \n ");
		sql.append(" on b.P00300001=d.dep_code  \n ");
		sql.append(" left join drgs.t_depgroup e  \n ");
		sql.append(" on e.depgroup_code=d.pardepgroup_code   \n ");
		sql.append(" left join drgs.t_drgs f  \n ");
		sql.append(" on f.drg_code=c.pardrg_code and f.ver_code=c.ver_code  \n ");
		sql.append(" left join drgs.t_adrg g  \n ");
		sql.append(" on g.adrg_code=f.adrg_code and f.ver_code=g.ver_code  \n ");
		sql.append(" left join drgs.t_drgversion v \n ");
		sql.append(" on v.ver_code=f.ver_code \n ");
		sql.append(" left join drgs.t_medins m  \n ");
		sql.append(" on m.parid=b.pmainindex  \n ");
		sql.append(" where 1=1 and f.drg_code is not null \n ");
		if(StringUtils.hasLength(depGroupCode)){
			sql.append(" and e.depgroup_code='"+depGroupCode+"' \n ");
		}
		if(StringUtils.hasLength(depCode)){
			sql.append(" and d.dep_code='"+depCode+"' \n ");
		}
		if(StringUtils.hasLength(adrgCode)){
			sql.append(" and g.adrg_code='"+adrgCode+"' \n ");
		}
		if("1".equals(isExceeding)){
			sql.append(" and b.P00800000>avgFeeStd.manage_benchmark \n ");
		}
		sql.append(" ) where discount=1 ");
		List<Map<String, Object>> list = jdbcTemplateObj.queryForList(sql.toString());
		return list;
	}

	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'findMedicalRecord' + #order.dateFrom + #order.dateTo + "
			+ "#order.adrgCode + #order.depGroupCode + #order.depCode + "
			+ "#order.isExceeding+#order.pType+#order.drgCode ")
	public List<Map<String, Object>> findMedicalRecord(Order order) {
		String pType = order.getpType();
		String dateFrom = order.getDateFrom();
		String dateTo = order.getDateTo();
		String depGroupCode = order.getDepGroupCode();
		String depCode = order.getDepCode();
		String adrgCode = order.getAdrgCode();
		String drgCode = order.getDrgCode();
		String isExceeding = order.getIsExceeding();
		StringBuilder sql = new StringBuilder();
		sql.append(" select  \n ");
		sql.append(" bs.P00220400 as mrNo,bs.P00060000 as patientName,BS.P00070001 as sex,BS.P00090000 as ageYear,mr.P00250000 as inDate, mr.P00290000 as outDate,mr.P00300000 as outdep,mr.P00620001 as outway,mr.P00320000 as indays, \n ");
		sql.append(" diag.PD0100 as diagnoseDessmain,mr.P00800000 as costamount, b.benchmark avgFeeStd, drgs_code drgCode, mr.pmainindex id \n ");
		sql.append(" from ( \n ");
		sql.append(" select distinct order_mr_id, benchmark, drgs_code from drgs.t_order_range_month \n ");
		sql.append(" and a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		if("1".equals(isExceeding)){
			sql.append(" and costamount>benchmark \n ");
		}else if("0".equals(isExceeding)){
			sql.append(" and costamount<=benchmark \n ");
		}
		sql.append(" and is_trans_dep=1 \n ");
		if(StringUtils.hasLength(depGroupCode) && !"DRG_DHCC".equals(depGroupCode)){
			sql.append(" and depgroup_code='"+depGroupCode+"'");
		}
		if(StringUtils.hasLength(depCode) && !"DRG_DHCC".equals(depCode)){
			sql.append(" and dep_code='"+depCode+"'");
		}
		if(StringUtils.hasLength(adrgCode) && !"DRG_DHCC".equals(adrgCode)){
			sql.append(" and adrg_code='"+adrgCode+"'");
		}
		if(StringUtils.hasLength(drgCode) && !"DRG_DHCC".equals(drgCode)){
			sql.append(" and drgs_code='"+drgCode+"'");
		}
		if("1".equals(pType)){
			sql.append(" and ismedins=1 \n ");
		}
		sql.append(" ) b left join drgs.mr_baseinfo bs \n ");
		sql.append(" on b.order_mr_id=bs.P00220400 \n ");//
		sql.append("LEFT JOIN drgs.MR_IPINFO mr ON bs.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" left join drgs.mr_expense_sum costsum on mr.pmainindex=costsum.pmainindex  \n ");
		sql.append(" LEFT JOIN drgs.MR_DIAGNOSE diag ON mr.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n ");
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	/**
	 * 
	 *  @Description：存储医嘱
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.application.OrderApplication#saveOrder(java.util.List, java.util.Map, java.util.Map)
	 */
	@Override
	public void saveOrder(List<Order> list,Map<String,Object> caseInfoMap,Map<String,String> orderFeeType) throws Exception{
		if(list.size()>0){
			for(Order o: list){
				String orderDocDepCode = "";
				String orderDocDep = o.getOrderDocDep();
				if(StringUtils.hasLength(orderDocDep)){
					String[] docDep = orderDocDep.split("-");
					if(docDep.length>1){
						orderDocDepCode = docDep[0];
						orderDocDep = docDep[1];
					}
				}else{
					orderDocDepCode = "";
					orderDocDep = "";
				}
				String orderReceiveDep = o.getOrderReceiveDep();
				String orderReceiveDepCode = "";
				if(StringUtils.hasLength(orderReceiveDep)){
					String[] receiveDep = orderReceiveDep.split("-");
					if(receiveDep.length>1){
						orderReceiveDepCode = receiveDep[0];
						orderReceiveDep = receiveDep[1];
					}
				}else{
					orderReceiveDepCode = "";
					orderReceiveDep = "";
				}
				StringBuilder sql = new StringBuilder();
				String id = UUID.randomUUID().toString();
				sql.append(" INSERT INTO DRGS.T_ORDER_RANGE_MONTH(ID,ORDER_MR_ID, ORDER_ID, ORDER_DESC, ORDER_TOTAL_FEE, ORDER_QUANTITY, ORDER_UNIT_PRICE,ORDER_DOCTOR, ORDER_DOC_DEP, ");
				sql.append(" ORDER_RECEIVE_DEP, ORDER_FIRSTPAGE_FEE, ORDER_SUBCATEGORY, ORDER_DOC_DEP_CODE, ORDER_RECEIVE_DEP_CODE,   ");
				sql.append(" OUT_DATE,DEP_CODE,DRGS_CODE,ISMEDINS,COSTAMOUNT,DCC_CODE,DEPGROUP_CODE,ADRG_CODE,IS_TRANS_DEP,BENCHMARK,CLINICGROUP,HOSP_CODE) \n");
				sql.append(" VALUES( '"+id+"','"+o.getOrderMrId()+"', '"+o.getOrderId()+"', '"+o.getOrderDesc()+"', "+o.getOrderTotalFee()+"," );
				sql.append(" "+o.getOrderQuantity()+", "+o.getOrderUnitPrice()+", '"+o.getOrderDoctor()+"', '"+orderDocDep+"', '"+orderReceiveDep+"', ");
				sql.append(" '"+o.getOrderFirstpageFee()+"', '"+o.getOrderSubcate()+"', '"+orderDocDepCode+"',  ");
				sql.append(" '"+orderReceiveDepCode+"',TO_DATE('"+caseInfoMap.get("OUTDATE")+"','YYYY-MM-DD HH24:MI:SS'),'"+caseInfoMap.get("OUTDEP")+"','"+caseInfoMap.get("DRGCODE")+"','"+caseInfoMap.get("ISMEDINS")+"', ");
				sql.append(" "+caseInfoMap.get("COSTAMOUNT")+",'"+orderFeeType.get(o.getOrderFirstpageFee())+"','"+caseInfoMap.get("DEPGROUPCODE")+"','"+caseInfoMap.get("ADRG_CODE")+"','"+caseInfoMap.get("ISTANSDEP")+"', ");
				sql.append(" "+caseInfoMap.get("BENCHMARK")+",'"+caseInfoMap.get("CLINICGROUP")+"','"+caseInfoMap.get("HOSPCODE")+"') \n");
				getRepository().createSqlQuery(sql.toString()).executeUpdate();
				getRepository().createSqlQuery(sql.toString()).executeUpdate();
				String sqlSpecial="";
				if(!"".equals(orderDocDepCode)){
					sqlSpecial="SELECT ORDER_DOC_DEP_CODE DEPCODE,ORDER_DOC_DEP DEPDESC FROM DRGS.T_ORDER_DEP WHERE ORDER_DOC_DEP_CODE='"+orderDocDepCode+"'";
				}
				else if(!"".equals(orderDocDep)){
					sqlSpecial="SELECT ORDER_DOC_DEP_CODE DEPCODE,ORDER_DOC_DEP DEPDESC FROM DRGS.T_ORDER_DEP WHERE ORDER_DOC_DEP='"+orderDocDep+"'";
				}
				if(!"".equals(sqlSpecial)){
					List<Map<String, Object>> specialDepList=jdbcTemplateObj.queryForList(sqlSpecial);
					if(specialDepList.size()<=0){
						sqlSpecial = "INSERT INTO DRGS.T_ORDER_DEP(ORDER_DOC_DEP_CODE, ORDER_DOC_DEP) VALUES('"+orderDocDepCode+"', '"+orderDocDep+"')";
						jdbcTemplateObj.execute(sqlSpecial);
					}
				}
			}
		}
	}
	/**
	 * 
	 *  @Description：保存医嘱后更新就诊ID
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.application.OrderApplication#updateMedicalrecord(java.lang.String, java.lang.String)
	 */
	@Override
	public void updateMedicalrecord(String pmainIndex, String orderMrId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" UPDATE DRGS.MR_BASEINFO T \n ");
		sql.append(" SET T.P00220400='"+orderMrId+"' \n ");
		sql.append(" WHERE T.PMAININDEX='"+pmainIndex+"' \n ");
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
	}
	/**
	 * 
	 *  @Description：通过病案号与入院日期查询PMAININDEX
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.application.OrderApplication#getCaseByMrNoIndate(java.lang.String, java.lang.String)
	 */
	@Override
	public Map<String,Object> getCaseByMrNoIndate(String mrNo,String inDate){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT BS.PMAININDEX FROM DRGS.MR_BASEINFO BS  LEFT JOIN DRGS.MR_IPINFO INFO ON BS.PMAININDEX=INFO.PMAININDEX \n ");
		sql.append("WHERE P00050000='"+mrNo+"' AND TO_CHAR(P00250000,'YYYY-MM-DD')='"+inDate+"' \n ");
		sql.append(" \n ");
		Map<String, Object> resultMap = jdbcTemplateObj.queryForMap(sql.toString());
		return resultMap;
	}
	/**
	 * 
	 *  @Description：查询医嘱明细表需要的相关病例信息
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.application.OrderApplication#getCaseInfo(java.lang.String)
	 */
	@Override
	public Map<String,Object> getCaseInfo(String pmainIndex,String stdId,String groupScheme){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT BS.P00010001 HOSPCODE,TO_CHAR(INFO.P00290000,'YYYY-MM-DD HH24:MI:SS') OUTDATE,P00300001 OUTDEP,PDG.PARDRG_CODE DRGCODE,P00800000 COSTAMOUNT,P00601001 CLINICGROUP, \n ");
		sql.append("CASE WHEN MEDIN.PARID IS NOT NULL THEN 1 ELSE 0 END ISMEDINS, \n ");
		sql.append("DEP.PARDEPGROUP_CODE DEPGROUPCODE,DRG.ADRG_CODE,STD.AS_AVG_FEE BENCHMARK, \n ");
		sql.append("CASE WHEN INFO.P00280001 IS NOT NULL OR P00280000 IS NOT NULL THEN 1 ELSE 0 END ISTANSDEP \n ");
		sql.append("from DRGS.MR_IPINFO info  \n ");
		sql.append("LEFT JOIN drgs.MR_BASEINFO BS ON INFO.PMAININDEX=BS.PMAININDEX \n ");
		sql.append("LEFT JOIN DRGS.T_PATIENTDRG PDG ON INFO.PMAININDEX=PDG.PARID  AND PDG.VER_REGION='"+groupScheme+"' \n ");
		sql.append("LEFT JOIN DRGS.T_MEDINS MEDIN ON INFO.PMAININDEX=MEDIN.PARID \n ");
		sql.append("LEFT JOIN DRGS.T_DEPARTMENT DEP ON INFO.P00300001 = DEP.DEP_CODE \n ");
		sql.append("LEFT JOIN DRGS.MR_EXPENSE_SUM COSTSUM ON INFO.PMAININDEX=COSTSUM.PMAININDEX \n ");
		sql.append("LEFT JOIN DRGS.T_DRGS DRG ON PDG.PARDRG_CODE = DRG.DRG_CODE AND PDG.VER_CODE = DRG.VER_CODE \n ");
		sql.append("LEFT JOIN DRGS.T_ANNUALSTD STD ON PDG.PARDRG_CODE = STD.AS_DRG_CODE AND STD.AS_ID='"+stdId+"' \n ");
		sql.append("WHERE INFO.PMAININDEX='"+pmainIndex+"' \n ");
		sql.append(" \n ");
		Map<String, Object> resultMap = jdbcTemplateObj.queryForMap(sql.toString());
		return resultMap;
	}
	/**
	 * 
	 *  @Description：查询医嘱费用分类与费用五大类的对应关系
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.application.OrderApplication#getOrderFeeType()
	 */
	@Override
	public Map<String,String> getOrderFeeType(){
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT DCC_CODE DCCCODE,ORDER_SCODE ORDERSCODE FROM DRGS.T_COSTSUB  \n ");
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		Map<String,String> resultMap=new HashMap<>();
		if(resultList.size()>0){
			for(Map<String, Object> map:resultList){
				resultMap.put(map.get("ORDERSCODE").toString(), map.get("DCCCODE").toString());
			}
		}
		return resultMap;
	}
	public int saveOrderDep(){
		String sqlSpecial="SELECT ORDER_DOC_DEP_CODE DEPCODE,ORDER_DOC_DEP DEPDESC FROM DRGS.T_ORDER_DEP WHERE IS_SPECIAL_DEP='1'";
		List<Map<String, Object>> specialDepList=jdbcTemplateObj.queryForList(sqlSpecial);
		
		String sql = "";
		sql += " BEGIN \n";
		sql +=      "DELETE FROM DRGS.T_ORDER_DEP ;\n";
		sql +=      "INSERT INTO DRGS.T_ORDER_DEP (ORDER_DOC_DEP_CODE,ORDER_DOC_DEP) SELECT DISTINCT ORDER_DOC_DEP_CODE,ORDER_DOC_DEP FROM DRGS.T_ORDER_RANGE_MONTH ;\n";
		for(int i=0;i<specialDepList.size();i++){
			sql +=	"UPDATE DRGS.T_ORDER_DEP SET IS_SPECIAL_DEP=1 WHERE ORDER_DOC_DEP_CODE='"+specialDepList.get(i).get("DEPCODE")+"' AND ORDER_DOC_DEP='"+specialDepList.get(i).get("DEPDESC")+"' ;\n";
		}
		sql += " 	COMMIT;\n";
		sql += " END;";
		int i=getRepository().createSqlQuery(sql).executeUpdate();
		return i;
	}
	
}
